package com.zbec.mdrs.repository.jpa;


import com.zbec.framework.security.entity.SysOrganization;
import com.zbec.mdrs.constants.Constants;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Repository
public class MbtDao {

    private SimpleJdbcTemplate simpleJdbcTemplate;
 

    public String  packSql(String  sql,Boolean restrainId,SysOrganization organization,String type){
        if(restrainId){
            sql = " and borrower.District_code like '32%' ";
        }

        if(organization.getOrgCode().equals("C1067132000013")){
            sql = sql+" and ("+type+".orgcode='3050A241003' or "+type+".orgcode='30508101019')";
        }else{
            sql = sql+" and ( "+type+".orgcode= '"+organization.getZzjgdm();
            if (organization.getOrgType().equals(Constants.BANK_TYPE_FH)){
                if(organization.getChildrenOrgs().size()>0){
                    List<SysOrganization> childOrg=organization.getChildrenOrgs();
                    for (int i = 0; i <childOrg.size(); i++) {
                        sql=sql+"' or "+type+".orgcode= '"+childOrg.get(i).getZzjgdm();
                    }
                    sql=sql+"')";
                }else{
                    sql=sql+"')";
                }
            }else{
                sql=sql+"')";
            }
        }

        if("bh".equals(type)){
            sql = sql + " group by bh.Baohan_kind,bh.Money_kind,bh.name";
        }
        return  sql;
    }

    public List findDraftsFromMBT(String beginDate, String endDate,Boolean restrainId,SysOrganization organization){
       String sql="select DISTINCT ba.orgcode,ba.Remitter_name,ba.Money_kind,ba.Bill_money,ba.Accept_date,\n" +
               "ba.Maturity_date,ba.Pay_date,ba.Deposit_scale,ba.Assure_flag,ba.diankuanflag,ba.Five_class,ba.ID,ba.card_no,ba.bill_no\n" +
               "from bankaccepts ba inner join borrowers borrower on borrower.orgcode = ba.orgcode and borrower.card_no = ba.card_no \n" +
               "where  '"+beginDate+"' <= ba.Maturity_date and  '"+endDate+"' >= ba.Accept_date and ba.bill_status<>3\n" +
               " and ba.operation_type  in (1,2,3)";

        sql=packSql(sql,restrainId,organization,"ba");

        System.out.println("drafts Sql==="+sql);
        return simpleJdbcTemplate.queryForList(sql);
    }


    public List findEntrustedLoanFromMBT(String beginDate, String endDate,Boolean restrainId,SysOrganization organization){
        String sql="select  bills.orgcode,bills.Money_kind,bills.Bill_money,bills.Bill_ye,bills.Create_date,bills.Maturity_date,bills.ID,bills.card_no,borrower.name_cn,bills.bill_no \n" +
                "from \n" +
                "loanbills bills inner join borrowers borrower on borrower.orgcode = bills.orgcode and borrower.card_no = bills.card_no \n" +
                "left join billexps exps on exps.orgcode = bills.orgcode and exps.loanCont_no = bills.loanCont_no \n" +
                "and exps.Bill_no = bills.bill_no and exps.card_no = bills.card_no and bills.Loan_property = '6'\n" +
                "where '"+beginDate+"' <= bills.Maturity_date and '"+endDate+"'>=bills.create_date \n" +
                "and bills.Loan_property = '6' and bills.Bill_ye>0 \n" +
                "and bills.operation_type  in (1,2,3) ";

        sql=packSql(sql,restrainId,organization,"bills");

        System.out.println("entrustedLoan Sql==="+sql);
        return simpleJdbcTemplate.queryForList(sql);
    }

    public List findGuaranteeFromMBT(String beginDate, String endDate,Boolean restrainId,SysOrganization organization){
       String sql=" select sum(case when bh.Ywdate >='"+beginDate+"' and bh.Ywdate<= '"+endDate+"' then 1 else 0 end) as currentCnt,\n" +
               "sum(case when bh.Ywdate  >='"+beginDate+"' and bh.Ywdate<= '"+endDate+"' then bh.Baohan_balance else 0 end) as currentSum,sum(1) as totalCnt,\n" +
               "bh.Ywdate,bh.Baohan_no,bh.orgcode,bh.Money_kind,bh.Baohan_kind,sum(bh.Baohan_balance) as Baohan_balance,\n"+
               " bh.Start_date,bh.End_date,bh.Five_class,bh.Baohan_money,bh.ID,bh.card_no  from baohans bh \n"+
               " inner join borrowers borrower on borrower.orgcode = bh.orgcode and borrower.card_no = bh.card_no \n" +
               "where '"+beginDate+"' <= bh.End_date and '"+endDate+"'>=bh.Start_date\n" +
               "and bh.operation_type  in (1,2,3) \n";

        sql=packSql(sql,restrainId,organization,"bh");
        System.out.println("guarantee Sql==="+sql);
        return simpleJdbcTemplate.queryForList(sql);
    }

    public List findDiscountDataFromMBT(String beginDate, String endDate,Boolean restrainId,SysOrganization organization){
        String sql = "select distinct borrower.certify_code, discount.Proposer_name,discount.ywdate,discount.Bill_kind,\n" +
                "\tdiscount.Acceptor_name,discount.acceptororgcode,discount.Money_kind,discount.Bill_money,\n" +
                "\tdiscount.Discount_date,discount.Accept_date,discount.Discount_money,\n" +
                "\tdiscount.Four_class, discount.Five_class,discount.orgcode,discount.id,discount.card_no,discount.bill_no\n" +
                "from (select max(bills.ywdate) as maxTime, bills.id as billsId from billdiscounts  bills " +
                "where bills.ywdate<='"+endDate+"' group by bills.bill_no order by bills.ywdate) as aa,\n" +
                "billdiscounts discount inner join borrowers borrower on borrower.orgcode = discount.orgcode and borrower.card_no = discount.card_no\n" +
                "where '"+beginDate+"' <= discount.Accept_date and '"+endDate+"'>=discount.Discount_date and discount.bill_status<>3" +
                " and aa.maxTime = discount.ywdate and aa.billsId = discount.id and  discount.operation_type  not  in (4,8,9)";


        sql=packSql(sql,restrainId,organization,"discount");

        System.out.println("finance Sql==="+sql);
        return simpleJdbcTemplate.queryForList(sql);
    }

    public List findBillDataFromMBT(String beginDate, String endDate, Boolean restrainId,SysOrganization organization){
        String condition = " ";

        condition=packSql(condition,restrainId,organization,"bills");

        String sql = "select  CONCAT(if(ensure.ensure_no is not null and ensure.valid_status =1 and ensure.ensure_money is not null, \"保证\", \"\" ) ,if(ensure.ensure_no is not null and ensure.valid_status =2 and ensure.ensure_money is null, \"信用\", \"\" ) , if (impawn.impawn_no is not null, \"质押\", \"\" ) , if(pledge.pledge_no is not null, \"抵押\" , \"\")) as dbfs,bills.create_date,bills.Maturity_date,\n" +
                "              exps.end_date,borrower.certify_code,bills.orgcode,borrower.name_cn,bills.bill_no,bills.money_kind,\n" +
                "\tbills.Loan_kind,bills.Bill_money,bills.Bill_ye,bills.create_date,bills.Maturity_date,\n" +
                "\tbills.Four_class,bills.Five_class,bills.ywdate,bills.id,bills.card_no,bills.loanCont_no,if(ensure.valid_status =1,if(ensure.ensure_style=2,sum(ensure.ensure_money),max(ensure.ensure_money)),null) as ensure_money," +
                "\timpawn_money,pledge_money, \n" +
                "\tif(ensure.valid_status =1,CONCAT(\"由\",ensure.ensure_name,\"提供的\",ensure.money_kind,if(ensure.ensure_style=2,sum(ensure.ensure_money),max(ensure.ensure_money)),\"元的保证\"),null) as ensure_desc,\n" +
                "\tif(impawn.impawn_money is not null,CONCAT(\"由\",impawn.impawn_name,\"提供的\",impawn.money_kind,\"金额为\"+impawn.impawn_money,\"元的\",impawn.impawn_kind,\"质押\"),null) as impawn_desc, \n" +
                "\tif(pledge.pledge_money is not null,CONCAT(\"由\",pledge.pledge_name,\"提供的\",pledge.money_kind,\"金额为\"+pledge.pledge_money,\"元的\",pledge.pledge_kind,\"抵押\"),null) as pledge_desc \n"+
                "from \n" +
                "loanbills bills inner join borrowers borrower on borrower.orgcode = bills.orgcode and borrower.card_no = bills.card_no\n" +
                "inner join billexps exps on exps.orgcode = bills.orgcode and exps.loanCont_no = bills.loanCont_no and exps.Bill_no = bills.bill_no and exps.card_no = bills.card_no and bills.Loan_property != '2'\n" +
                "left  join (select ens.valid_status as valid_status, ens.ensure_money as ensure_money,\n" +
                "ens.ensure_name as ensure_name,ens.ensure_no as ensure_no,ens.orgcode as orgcode,ens.contract_no as contract_no,\n" +
                "ens.card_no as card_no,ens.ensure_style as ensure_style,ens.operation_type as operation_type, dict.valdesc as money_kind from ensurecontracts ens \n" +
                "inner join dictionarys dict on dict.codetype='1501' and dict.code = ens.money_kind \n" +
                "where ens.operation_type  in (1,2,3) and ((ens.valid_status =1 and ens.ensure_money is not null) or (ens.valid_status =2 and ens.ensure_money is null)) ) as ensure on ensure.orgcode = bills.orgcode \n" +
                "and ensure .contract_no = bills.loanCont_no  and ensure .card_no =   bills.card_no \n" +
                "left join (select imp.impawn_money as impawn_money,\n" +
                "imp.impawn_name as impawn_name,imp.impawn_no as impawn_no,imp.orgcode as orgcode,imp.contract_no as contract_no,\n" +
                "imp.card_no as card_no,dict.valdesc as impawn_kind,dict1.valdesc as money_kind from impawncontract imp \n" +
                "inner join dictionarys dict on dict.codetype='7621' and dict.code = imp.impawn_kind \n" +
                "inner join dictionarys dict1 on dict1.codetype='1501' and dict1.code = imp.money_kind \n" +
                "where imp.operation_type in (1,2,3) and imp.valid_status=1) impawn on impawn.orgcode = bills.orgcode  \n" +
                "and impawn.contract_no = bills.loanCont_no  and impawn.card_no =   bills.card_no  \n" +
                "left join (select pld.pledge_money as pledge_money,\n" +
                "pld.pledge_name as pledge_name,pld.pledge_no as pledge_no,pld.orgcode as orgcode,pld.contract_no as contract_no,\n" +
                "pld.card_no as card_no,dict.valdesc as pledge_kind,dict1.valdesc as money_kind from pledgecontracts pld \n" +
                "inner join dictionarys dict on dict.codetype='7617'and dict.code = pld.pledge_kind\n" +
                "inner join dictionarys dict1 on dict1.codetype='1501' and dict1.code = pld.money_kind \n" +
                "where pld.operation_type  in (1,2,3) and pld.valid_status=1) as pledge \n" +
                "on pledge.orgcode = bills.orgcode  and pledge.contract_no = bills.loanCont_no  and pledge.card_no=bills.card_no \n" +
                "\n" +
                "where '"+beginDate+"' <= exps.end_date and '"+endDate+"'>=bills.create_date and bills.Bill_ye>0" +
                "\n" +
                "and bills.Loan_property != '2' \n" +
                "and bills.operation_type  in (1,2,3) \n"+
                "and bills.bill_no not like 'ENT%' \n"+
                "and bills.bill_no not like 'PO%' \n"
                +condition+
                " group by bills.bill_no,ensure.ensure_style \n"+
                "\n" +
                " union\n" +
                "\n" +
                "select  CONCAT(if(ensure.ensure_no is not null and ensure.valid_status =1 and ensure.ensure_money is not null, \"保证\", \"\" ) ,if(ensure.ensure_no is not null and ensure.valid_status =2 and ensure.ensure_money is null, \"信用\", \"\" ) , if (impawn.impawn_no is not null, \"质押\", \"\" ) , if(pledge.pledge_no is not null, \"抵押\", \"\")) as dbfs,bills.create_date,bills.Maturity_date,\n" +
                "              exps.end_date,borrower.certify_code,bills.orgcode,borrower.name_cn,bills.bill_no,bills.money_kind,\n" +
                "\tbills.Loan_kind,bills.Bill_money,bills.Bill_ye,bills.create_date,bills.Maturity_date,\n" +
                "\tbills.Four_class,bills.Five_class,bills.ywdate,bills.id,bills.card_no,bills.loanCont_no,if(ensure.valid_status =1,if(ensure.ensure_style=2,sum(ensure.ensure_money),max(ensure.ensure_money)),null) as ensure_money," +
                "\timpawn_money,pledge_money, \n" +
                "\tif(ensure.valid_status =1,CONCAT(\"由\",ensure.ensure_name,\"提供的\",ensure.money_kind,if(ensure.ensure_style=2,sum(ensure.ensure_money),max(ensure.ensure_money)),\"元的保证\"),null) as ensure_desc,\n" +
                "\tif(impawn.impawn_money is not null,CONCAT(\"由\",impawn.impawn_name,\"提供的\",impawn.money_kind,\"金额为\"+impawn.impawn_money,\"元的\",impawn.impawn_kind,\"质押\"),null) as impawn_desc, \n" +
                "\tif(pledge.pledge_money is not null,CONCAT(\"由\",pledge.pledge_name,\"提供的\",pledge.money_kind,\"金额为\"+pledge.pledge_money,\"元的\",pledge.pledge_kind,\"抵押\"),null) as pledge_desc \n"+
                "from \n" +
                "loanbills bills inner join borrowers borrower on borrower.orgcode = bills.orgcode and borrower.card_no = bills.card_no\n" +
                "left join billexps exps on exps.orgcode = bills.orgcode and exps.loanCont_no = bills.loanCont_no and exps.Bill_no = bills.bill_no and exps.card_no = bills.card_no and bills.Loan_property != '2'\n" +
                "left  join (select ens.valid_status as valid_status, ens.ensure_money as ensure_money,\n" +
                "ens.ensure_name as ensure_name,ens.ensure_no as ensure_no,ens.orgcode as orgcode,ens.contract_no as contract_no,\n" +
                "ens.card_no as card_no,ens.ensure_style as ensure_style,ens.operation_type as operation_type, dict.valdesc as money_kind from ensurecontracts ens \n" +
                "inner join dictionarys dict on dict.codetype='1501' and dict.code = ens.money_kind \n" +
                "where ens.operation_type  in (1,2,3) and ((ens.valid_status =1 and ens.ensure_money is not null) or (ens.valid_status =2 and ens.ensure_money is null)) ) as ensure on ensure.orgcode = bills.orgcode \n" +
                "and ensure .contract_no = bills.loanCont_no  and ensure .card_no =   bills.card_no \n" +
                "left join (select imp.impawn_money as impawn_money,\n" +
                "imp.impawn_name as impawn_name,imp.impawn_no as impawn_no,imp.orgcode as orgcode,imp.contract_no as contract_no,\n" +
                "imp.card_no as card_no,dict.valdesc as impawn_kind,dict1.valdesc as money_kind from impawncontract imp \n" +
                "inner join dictionarys dict on dict.codetype='7621' and dict.code = imp.impawn_kind \n" +
                "inner join dictionarys dict1 on dict1.codetype='1501' and dict1.code = imp.money_kind \n" +
                "where imp.operation_type in (1,2,3) and imp.valid_status=1) impawn on impawn.orgcode = bills.orgcode  \n" +
                "and impawn.contract_no = bills.loanCont_no  and impawn.card_no =   bills.card_no  \n" +
                "left join (select pld.pledge_money as pledge_money,\n" +
                "pld.pledge_name as pledge_name,pld.pledge_no as pledge_no,pld.orgcode as orgcode,pld.contract_no as contract_no,\n" +
                "pld.card_no as card_no,dict.valdesc as pledge_kind,dict1.valdesc as money_kind from pledgecontracts pld \n" +
                "inner join dictionarys dict on dict.codetype='7617'and dict.code = pld.pledge_kind\n" +
                "inner join dictionarys dict1 on dict1.codetype='1501' and dict1.code = pld.money_kind \n" +
                "where pld.operation_type  in (1,2,3) and pld.valid_status =1) as pledge \n" +
                "on pledge.orgcode = bills.orgcode  and pledge.contract_no = bills.loanCont_no  and pledge.card_no=bills.card_no \n" +
                "\n" +
                "where '"+beginDate+"' <= bills.Maturity_date and '"+endDate+"'>=bills.create_date and bills.Bill_ye>0 " +
                "\n" +
                "and bills.Loan_property != '2' \n" +
                "and bills.operation_type  in (1,2,3) \n"+
                "and bills.bill_no not like 'ENT%' \n"+
                "and bills.bill_no not like 'PO%' \n"
                +condition+
                " group by bills.bill_no,ensure.ensure_style \n"+
                "";
        System.out.println("loan Sql==="+sql);
        return simpleJdbcTemplate.queryForList(sql);
    }

    public List findFinanceDataFromMBT(String beginDate, String endDate, Boolean restrainId,SysOrganization organization){
        String condition = " ";

        condition=packSql(condition,restrainId,organization,"finance");

        String sql = "select distinct \n" +
                " finance.Start_date as create_date,\n" +
                " finance.End_date as Maturity_date,\n" +
                " exps.end_date as end_date,\n" +
                " borrower.certify_code ,\n" +
                " finance.orgcode,\n" +
                " finance.name as name_cn,\n" +
                " finance.yw_no as bill_no,\n" +
                " finance.money_kind,\n" +
                " finance.card_no,\n" +
                " finance.Yw_kind as Loan_kind,\n" +
                " finance.Finance_money as Bill_money,\n" +
                " finance.Finance_balance as Bill_ye,\n" +
                "        finance.Four_class,\n" +
                "    finance.Five_class,\n" +
                "    finance.ywdate,finance.id            \n" +
                "from \n" +
                " financebusiness finance inner join borrowers borrower on borrower.orgcode = finance.orgcode and borrower.card_no = finance.card_no\n" +
                " inner join financeexps exps on exps.orgcode = finance.orgcode \n" +
                " and exps.Proto_no = finance.Proto_no \n" +
                " and exps.card_no = finance.card_no and finance.Exp_flag !=2\n" +
                " and exps.yw_no = finance.yw_no " +
                "\n" +
                " where '"+beginDate+"' <= exps.end_date and '"+endDate+"'>= finance.Start_date" +
                "\n" +
                " and finance.operation_type  in (1,2,3)  \n"
                 +condition+
                "\n" +
                " union\n" +
                "\n" +
                " select  distinct\n" +
                " finance.Start_date as create_date,\n" +
                " finance.End_date as Maturity_date,\n" +
                " exps.end_date as end_date,\n" +
                " borrower.certify_code ,\n" +
                " finance.orgcode,\n" +
                " finance.name as name_cn,\n" +
                " finance.yw_no as bill_no,\n" +
                " finance.money_kind,\n" +
                " finance.card_no,\n" +
                " finance.Yw_kind as Loan_kind,\n" +
                " finance.Finance_money as Bill_money,\n" +
                " finance.Finance_balance as Bill_ye,\n" +
                "        finance.Four_class,\n" +
                "    finance.Five_class,\n" +
                "    finance.ywdate,finance.id              \n" +
                "from \n" +
                " financebusiness finance inner join borrowers borrower on borrower.orgcode = finance.orgcode and borrower.card_no = finance.card_no\n" +
                " left join financeexps exps on exps.orgcode = finance.orgcode \n" +
                " and exps.Proto_no = finance.Proto_no and exps.card_no = finance.card_no \n" +
                " and finance.Exp_flag !=2\n" +
                " and exps.yw_no = finance.yw_no " +
                "\n" +
                 " where '"+beginDate+"' <= finance.End_date and '"+endDate+"'>= finance.Start_date" +
                "\n" +
                " and finance.operation_type  in (1,2,3) \n"
                +condition+
                "";
        System.out.println("finance Sql==="+sql);
       return simpleJdbcTemplate.queryForList(sql);
    }

    public List findBaoliDataFromMBT(String beginDate, String endDate, Boolean restrainId,SysOrganization organization){
        String condition = " ";

        condition=packSql(condition,restrainId,organization,"baoli");

        String sql = "select DISTINCT\n" +
                " baoli.loanCont_date as create_date,\n" +
                " borrower.certify_code ,\n" +
                " baoli.orgcode,\n" +
                " baoli.name as name_cn,\n" +
                " baoli.loanCont_no as bill_no,\n" +
                " baoli.money_kind,\n" +
                " baoli.card_no,\n" +
                " baoli.Baoli_type as Loan_kind,\n" +
                " baoli.Money as Bill_money,\n" +
                " baoli.loanCont_balance as Bill_ye,\n" +
                " baoli.Four_class,\n" +
                " baoli.Five_class,\n" +
                " baoli.ywdate,baoli.id,baoli.card_no   \n" +
                "from baolis baoli\n" +
                "inner join borrowers borrower on borrower.orgcode = baoli.orgcode and borrower.card_no = baoli.card_no \n" +
                " where '"+endDate+"'>= baoli.loanCont_date"
                +condition+
                " and baoli.operation_type  in (1,2,3)  ";
        System.out.println("baoli Sql==="+sql);
       return simpleJdbcTemplate.queryForList(sql);

    }

    public void setDataSource(DataSource dataSource){
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

}